package com.example.wordlistdemo.dao;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.wordlistdemo.WordEntity;
import com.example.wordlistdemo.util.ExcelHelper;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "test.db";
    private static final int DATABASE_VERSION = 1;

    // 构造方法
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建数据库表的 SQL 语句
        String createTableQuery = "CREATE TABLE IF NOT EXISTS word_list (" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "english TEXT," +
                "chinese TEXT," +
                "phonetic TEXT," +
                "isHide INTEGER DEFAULT 0," +
                "familiar INTEGER DEFAULT 0)";

        // 执行创建表的 SQL 语句
        db.execSQL(createTableQuery);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库的逻辑，如果有
    }

    /**
     * 根据单词首字母查询单词
     * @param letter
     * @return
     */
    public List<WordEntity> getWordsByLetter(String letter) {
        List<WordEntity> wordList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        // 在这里执行数据库查询
        String query = "SELECT * FROM word_list WHERE familiar = 0 and english LIKE '" + letter + "%'";
        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            do {
                // 从数据库结果中获取单词信息，例如英文单词、中文翻译等
                @SuppressLint("Range") String englishWord = cursor.getString(cursor.getColumnIndex("english"));
                @SuppressLint("Range") String chineseTranslation = cursor.getString(cursor.getColumnIndex("chinese"));
                @SuppressLint("Range") String pronunciation = cursor.getString(cursor.getColumnIndex("phonetic"));
                @SuppressLint("Range") int familiar = cursor.getInt(cursor.getColumnIndex("familiar"));
                @SuppressLint("Range") int isHide = cursor.getInt(cursor.getColumnIndex("isHide"));
                // 创建 Word 对象并添加到列表中
                WordEntity word = new WordEntity(englishWord, chineseTranslation, pronunciation, familiar,isHide);
                wordList.add(word);
            } while (cursor.moveToNext());
        }
        // 关闭数据库连接
        cursor.close();
        db.close();
        return wordList;
    }

    /**
     * 是否熟悉
     * @param englishWord
     * @param isFamiliar
     */
    public  void updateFamiliarity(String englishWord, int isFamiliar) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("familiar", isFamiliar); // 如果熟悉，设置为1；否则，设置为0

        // 更新数据库中的记录
        db.update("word_list", values, "english=?", new String[]{englishWord});

        // 关闭数据库连接
        db.close();
    }

    /**
     * 是否隐藏
     * @param englishWord
     * @param isHide
     */
    public  void updateIsHide(String englishWord, int isHide) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        // 是否隐藏 0否，1是
        values.put("isHide", isHide);

        // 更新数据库中的记录
        db.update("word_list", values, "english=?", new String[]{englishWord});

        // 关闭数据库连接
        db.close();
    }

    /**
     * 展示所有不熟悉的单词
     * @return
     */
    public List<WordEntity> listNoFamiliar(){
        String query = "SELECT * FROM word_list where familiar=0";
        return this.queryList(query);
    }

    /**
     * 展示所有熟悉的单词
     * @return
     */
    public List<WordEntity> listFamiliar() {
        String query = "SELECT * FROM word_list where familiar=1";
        return this.queryList(query);
    }

    /**
     * 展示所有单词
     * @return
     */
    public List<WordEntity> listAll(){
        String querySql = "SELECT * FROM word_list order by english asc";
        return this.queryList(querySql);
    }

    private List<WordEntity> queryList(String querySql){
        List<WordEntity> wordList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(querySql, null);
        // 遍历查询结果并将数据加入到 List 中
        while (cursor.moveToNext()) {
            @SuppressLint("Range") String english = cursor.getString(cursor.getColumnIndex("english"));
            @SuppressLint("Range") String chinese = cursor.getString(cursor.getColumnIndex("chinese"));
            @SuppressLint("Range") String phonetic = cursor.getString(cursor.getColumnIndex("phonetic"));
            @SuppressLint("Range") int familiar = cursor.getInt(cursor.getColumnIndex("familiar"));
            @SuppressLint("Range") int isHide = cursor.getInt(cursor.getColumnIndex("isHide"));
            WordEntity word = new WordEntity(english, chinese, phonetic, familiar,isHide);
            wordList.add(word);
        }
        cursor.close();
        db.close();
        return wordList;
    }

    /**
     * 批量导入数据
     * @param wordList
     */
    public void insertDataBatch(List<WordEntity> wordList) {
        this.delAllData();
        // 将数据插入数据库
        SQLiteDatabase db = this.getWritableDatabase();
        for (WordEntity word : wordList) {
            ContentValues values = new ContentValues();
            values.put("english", word.getEnglish());
            values.put("chinese", word.getChinese());
            values.put("phonetic", word.getPhonetic());
            values.put("familiar", word.isFamiliar() ? 1 : 0);
            values.put("isHide",word.isHide()? 1 : 0);
            // 插入数据
            db.insert("word_list", null, values);
        }
        // 关闭数据库连接
        db.close();
    }

    /**
     * 删除所有数据数据
     */
    public void delAllData() {
        // 查询数据库中是否有数据
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "word_list";
        db.delete(query, null, null);
        System.out.println("数据删除成功！");
    }
}

